/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package DBController;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import model.UserBean;

/**
 *
 * @author brlnt
 */
public class UserDB extends DBClass {

    private String sql;
    private UserBean dataUser;
    Connection con;
    Statement stmt;
    ResultSet rs;

    private void connection() {
        try {
            con = createConnection();
            stmt = con.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(UserDB.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UserDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ResultSet doQuery(String sql) {
        try {
            connection();
            rs = stmt.executeQuery(sql);
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(UserDB.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    private void doUpdate(String sql) {
        try {
            connection();
            stmt.executeUpdate(sql);
        } catch (SQLException ex) {
            Logger.getLogger(UserDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    private void closeCon() {
        try {
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(UserDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /**
     *
     * @param email
     * @param password
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public boolean cekLogin(String email, String password) throws SQLException, ClassNotFoundException {
        con = createConnection();
        stmt = con.createStatement();
        sql = "SELECT * from `user` WHERE email='" + email + "' AND password=MD5('" + password + "');";
        rs = stmt.executeQuery(sql);
        if (rs.next()) {
            closeCon();
            return true;
        } else {
            closeCon();
            return false;
        }
    }

    /**
     *
     * @param email
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public UserBean getUserByEmail(String email) throws SQLException, ClassNotFoundException {
        sql = "SELECT * from `user` WHERE email='" + email + "'";
        return getUser(sql);
    }

    /**
     *
     * @param sql
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    private UserBean getUser(String sql) throws SQLException, ClassNotFoundException {
        connection();
        rs = stmt.executeQuery(sql);
        dataUser = new UserBean();
        while (rs.next()) {
            dataUser.setIdUser(rs.getInt(1));
            dataUser.setFirstName(rs.getString(2));
            dataUser.setLastName(rs.getString(3));
            dataUser.setEmail(rs.getString(4));
            dataUser.setRole(rs.getInt(6));
            //dataUser.setPassword(rs.getString(6));
        }
        closeCon();
        return dataUser;
    }
    
    public UserBean getUserByID(int id){
        try {
            sql = "SELECT * FROM `user` WHERE ID_USER="+id;
            connection();
            rs = doQuery(sql);
            UserBean dataUser = new UserBean();
            
            while(rs.next()){
                dataUser.setIdUser(rs.getInt(1));
                dataUser.setFirstName(rs.getString(2));
                dataUser.setLastName(rs.getString(3));
                dataUser.setEmail(rs.getString(4));
                dataUser.setPassword(rs.getString(5));
                dataUser.setRole(rs.getInt(6));
            }
            return dataUser;
        } catch (SQLException ex) {
            Logger.getLogger(UserDB.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
    public List getAllUser() throws ClassNotFoundException, SQLException {
        sql = "SELECT u.`ID_USER`, u.`FIRST_NAME`, u.`LAST_NAME`, u.`EMAIL`, u.ROLE, r.`N_ROLE` "
                + "FROM user u "
                + "INNER JOIN ROLE r "
                + "ON u.ROLE = r.ID_ROLE";
        connection();
        rs = stmt.executeQuery(sql);
        List listUser = new ArrayList();

        UserBean dataUser;

        while (rs.next()) {
            dataUser = new UserBean();
            dataUser.setIdUser(rs.getInt(1));
            dataUser.setFirstName(rs.getString(2));
            dataUser.setLastName(rs.getString(3));
            dataUser.setEmail(rs.getString(4));
            dataUser.setRole(rs.getInt(5));
            dataUser.setnRole(rs.getString(6));
            listUser.add(dataUser);
        }
        closeCon();
        return listUser;
    }

    public void createUser(UserBean dataUser) {
        sql = "INSERT INTO `user`(`FIRST_NAME`, `LAST_NAME`, `EMAIL`, `ROLE`, `PASSWORD`) "
                + "VALUES ("
                + "'" + dataUser.getFirstName() + "',"
                + "'" + dataUser.getLastName() + "',"
                + "'" + dataUser.getEmail() + "',"
                + "'" + dataUser.getRole() + "',"
                + "MD5('" + dataUser.getPassword() + "')"
                + ");";
        doUpdate(sql);
        closeCon();
    }

    public boolean cekPassword(int id, String password) {
        try {
            sql = "SELECT password FROM user WHERE id_user="+id+" AND password=md5('"+password+"')";
            rs = doQuery(sql);
            return rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(UserDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public void editUser(UserBean dataUser) {
        sql = "UPDATE `user` SET "
                + "`FIRST_NAME`='" + dataUser.getFirstName() + "',"
                + "`LAST_NAME`='" + dataUser.getLastName() + "',"
                + "`EMAIL`='"+ dataUser.getEmail() + "' "
                + " WHERE `ID_USER`=" + dataUser.getIdUser();
        doUpdate(sql);
        closeCon();
    }

    public void deleteUser(int idUser) {
        sql = "DELETE FROM `user` WHERE ID_USER="+idUser;
        doUpdate(sql);
        closeCon();
    }

    public void ubahRole(int id, int role) {
         sql = "UPDATE `user` SET "
                 + "ROLE="+role+" "
                 + "WHERE ID_USER="+id+" ";
         doUpdate(sql);
         closeCon();
    }
    
    public boolean cekEmail(String email){
        try {
            sql = "SELECT email FROM `user` WHERE email='"+email+"';";
            rs = doQuery(sql);
            return rs.next();
        } catch (SQLException ex) {
            Logger.getLogger(UserDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public void editUserWithPass(UserBean dataUser) {
        sql = "UPDATE `user` SET "
                + "`FIRST_NAME`='" + dataUser.getFirstName() + "',"
                + "`LAST_NAME`='" + dataUser.getLastName() + "',"
                + "`EMAIL`='"+ dataUser.getEmail() + "',"
                + "`PASSWORD`=md5('"+ dataUser.getPassword()+ "') "
                + " WHERE `ID_USER`=" + dataUser.getIdUser();
        doUpdate(sql);
        closeCon();
    }
}
